#ods - dwd 首日装载语句


#!/bin/bash
#两个参数
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else
   echo "请传入日期参数"
   exit
fi

dwd_user_register_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table edu.dwd_user_register_inc partition(dt) 
select
    ui.user_id,
    date_format(ui.create_time,'yyyy-MM-dd') date_id,
    ui.create_time,
    source_id,
    bp.province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system,
    date_format(create_time,'yyyy-MM-dd') 
from
(
    select
        data.id user_id,
        data.create_time create_time
    from ${APP}.ods_user_info_inc
    where dt='$do_date'
     and type='bootstrap-insert'
)ui
left join
(
    select
        common.ar area_code,
        common.ba brand,
        common.sc source_id,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and common.uid is not null
)log
on ui.user_id=log.user_id
left join
(
    select
        id province_id,
        area_code
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.area_code;
"
dwd_trade_pay_detail_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table edu.dwd_trade_pay_detail_inc partition (dt)
select
    id
    ,od.order_id
    ,user_id
    ,payment_status
    ,total_amount
    ,date_format(callback_time, 'yyyy-MM-dd') date_id
    ,callback_time
    ,date_format(callback_time,'yyyy-MM-dd')
from
(
    select
        data.id
        ,data.order_id
        ,data.payment_status
        ,data.total_amount
        ,data.callback_time
    from edu.ods_payment_info_inc
    where dt='$do_date'
    and type='bootstrap-insert'
    and data.payment_status='1602'
) pi
left join
(
    select
        data.order_id
        ,data.user_id
    from edu.ods_order_detail_inc
    where dt = '$do_date'
    and type = 'bootstrap-insert'
) od
on od.order_id=pi.order_id;
"
dwd_trade_order_detail_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite ${APP}.table dwd_trade_order_detail_inc partition (dt)
select
  od.id
  ,order_id
  ,user_id
  ,course_id
  ,province_id
  ,date_format(create_time, 'yyyy-MM-dd') date_id
  ,create_time
  ,origin_amount
  ,coupon_reduce
  ,final_amount
  ,source_id
  ,source_site
  ,source_url
  ,date_format(create_time,'yyyy-MM-dd')
from
(
  select
    data.id
    ,data.order_id
    ,data.user_id
    ,data.course_id
    ,data.province_id
    ,data.create_time
    ,data.origin_amount
    ,data.coupon_reduce
    ,data.final_amount
  from ${APP}.ods_order_detail_inc
  where dt = '$do_date'
  and type = 'bootstrap-insert'
) od
left join
(
  select
    data.id,
    data.province_id,
    data.session_id session_id
  from ${APP}.ods_order_info_inc
  where dt = '$do_date'
  and type = 'bootstrap-insert'
) oi
on od.order_id = oi.id
left join
(
  select
    common.sc sc,
    common.sid sid
  from ${APP}.ods_log_inc
  where dt = '$do_date'
)log
on oi.session_id = log.sid
left join
(
  select
    id source_id,
    source_site,
    source_url
  from ${APP}.ods_base_source_full
  where dt = '$do_date'
)bs
on log.sc = bs.source_id;
"
dwd_interaction_questiontest_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_interaction_questiontest_inc partition (dt)
SELECT
  t1.id,
  t1.user_id,
  t1.paper_id,
  paper_title ,
  course_id,
  course_name,
  question_id,
  date_format(t1.create_time, 'yyyy-MM-dd') date_id,
  id_correct,
  score,
  duration_sec,
 date_format(t1.create_time, 'yyyy-MM-dd')
  FROM (
  SELECT
    data.id ,
    data.user_id,
    data.paper_id,
    data.question_id,
    data.id_correct,
    data.create_time
    FROM ${APP}.ods_test_exam_question_inc
    WHERE dt='$do_date'
    and TYPE='bootstrap-insert'
    ) t1
    join (
    SELECT
      data.paper_id,
      data.duration_sec,
      data.score
       FROM ${APP}.ods_test_exam_inc
       WHERE dt='$do_date'
       and TYPE='bootstrap-insert')t2
      ON t1.paper_id=t2.paper_id
    join (
    SELECT
      id,
      paper_title,
      course_id
       FROM ${APP}.ods_test_paper_full
       WHERE dt='$do_date') t3
      ON t3.id=t1.paper_id
    join(
      SELECT
        id,
        course_name
        FROM ${APP}.ods_course_info_full
        WHERE dt='$do_date') t4
      ON t4.id=t3.course_id;
"
dwd_trade_cart_add_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table edu.dwd_trade_cart_add_inc partition (dt)
select
    id,
    user_id,
    course_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    date_format(create_time, 'yyyy-MM-dd')
from
(
    select
        data.id,
        data.user_id,
        data.course_id,
        data.create_time
    from edu.ods_cart_info_inc
    where dt = '$do_date'
    and type = 'bootstrap-insert'
)ci;"

case $1 in
    "dwd_user_register_inc" )
        hive -e "$dwd_user_register_inc"
    ;;
    "dwd_trade_pay_detail_suc_inc" )
        hive -e "$dwd_trade_pay_detail_suc_inc"   
    ;;
    "dwd_trade_order_detail_inc" )
        hive -e "$dwd_trade_order_detail_inc"
    ;;   
    "dwd_interaction_questiontest_inc" )
    hive -e "$dwd_interaction_questiontest_inc"
    ;;    
    "dwd_trade_cart_add_inc" )
    hive -e "$dwd_trade_cart_add_inc"
    ;;   
    "all" )
        hive -e "$dwd_trade_pay_detail_suc_inc$dwd_trade_order_detail_inc$dwd_video_audition_play_inc$dwd_interaction_questiontest_inc"
    ;;
esac

